using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Data;
using System.Data.Linq;
using System.IO;
using System.Linq;
using System.Linq.Dynamic;
using System.Net;
using System.Text;
using System.Text.RegularExpressions;
using System.Web;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.Caching;
using System.Web.Script.Services;
using System.Web.Services;
using System.Xml;
using XLinq = System.Xml.Linq;
using Data;
using Data.DataSources;
using Data.LINQ;
using Dimok.BlackBook;
using Dimok.Data;
using Dimok.Data.Database;
using Dimok.Exceptions;
using System.Web.UI.WebControls;
 
namespace Dimok.Aspx.Manager.Lib {
	[ScriptService]
	[WebService(Namespace = "http://motobankcorp/manager/Admin/")]
	[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
	public class WS : Dimok.Menu.Admin.WS, IBlackBook {
		static WS() {
			_mmr = Manheim.ManheimClient.Activate();
		}
		new protected UOX uox = new UOX();
		protected int applID { get { return uox.applID; } }
		protected string db { get { return uox.db; } }
		protected string dbADO { get { return uox.dbADO; } }

		#region Accurint
		[WebMethod]
		public string[] Accurint_InstantID(string firstName, string lastName, string SSN, string street, string city, string state, string zip, string DOB, string homePhone, string driverLicense) {
			try {
				return uox.Accurint.InstantID(firstName, lastName, SSN, street, city, state, zip, DOB, homePhone, driverLicense);
			} catch (Exception exc) { throw new ClientException(exc); }
		}

		[WebMethod]
		public string[] Accurint_InstantIDByLoanID(int loanID) {
			try {
				if (loanID == 0) loanID = uox.applID;
				return uox.Accurint.InstantID(loanID);
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		[WebMethod]
		public string Accurint_DriverLicense(string driverLicense) {
			try {
				return UOX.AccurintClass.DriverLicense(driverLicense);
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		[WebMethod]
		public string Accurint_GoPeopleSearch(string SSN) {
			try {
				return UOX.AccurintClass.GoPeopleSearch(SSN).DataSet.GetXml();
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		[WebMethod]
		public string Accurint_GoPeopleAtWork(string SSN) {
			try {
				return UOX.AccurintClass.GoPeopleAtWork(SSN).DataSet.GetXml();
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		#endregion

		#region DMV
		[WebMethod]
		public void FetchTitleByVIN_Raw(string vin) {
			try {
				string[] ret = DMV.Remote.Activate().FetchTitleByVIN(vin);
				HttpContext.Current.Response.Write(ret[ret.Length - 1]);
			} catch (Exception exc) { throw new ClientException(exc); }

		}
		[WebMethod]
		public string[] FetchTitleByVIN(string vin) {
			try {
				return DMV.Remote.Activate().FetchTitleByVIN(vin);
			} catch (Exception exc) { throw new ClientException(exc); }
			//try{
			//HttpWebRequest wrec = WebRequest.Create(url);
			//wrec.
			//HTMLDocumentClass doc = new HTMLDocumentClass();
			//doc.createDocumentFromUrl(url,null);
			//object n = null;
			//SHDocVw.InternetExplorerClass ie = new SHDocVw.InternetExplorerClass();
			//ie.Navigate(url,ref n,ref n,ref n,ref n);
			//GoWaitIE(ie);
			//HttpContext.Current.Response.Write(((HTMLDocument)ie.Document).body.outerHTML);
			//}catch(Exception exc){
			//	HttpContext.Current.Response.Write(ClientException.GetExceptions(exc));
			//}
			//return;
			WebClient wc = new WebClient();
			NameValueCollectionEx nvc = new NameValueCollectionEx("ObjectSpace$Global$0$VIN", "lklkjlkj");//"JH4UA2653WC004630");
			nvc.Add("ObjectSpace$Global$0$TObject", "fCo01LQV000000000000000200003U0000I00001000000400000");
			nvc.Add("NeuFormEncodingField", "ISO-8859-1");
			nvc.Add("SubmitBy", "mvchecking");
			nvc.Add("SubmitControl", "ObjectSpace$Global$0$dhsmvSubmitBtn_2");
			string response = Encoding.ASCII.GetString(wc.UploadValues(Config.DMV.WebSiteUrl, "POST", nvc));
			HttpContext.Current.Response.Write(response);
		}
		#endregion

		#region Geocoder
		[WebMethod]
		public DataSet Geocoder(string street, string city, string state, string zip) {
			try {
				return Dimok.Geocoder.Fetcher.Fetch(street, city, state, zip);
			} catch (Exception exc) { throw new ClientException(exc); }
		}

		#endregion

		#region Menu Floater
		[WebMethod]
		public string TrackerRequestsOutlook(string vin) {
			try {
				return SQL.ADOToString(UOX.dbRawAdo, "SELECT * FROM v_TrackerRequests_Outlook ORDER BY [Count] DESC");
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		[WebMethod]
		public string TrackersOutlook(string vin) {
			try {
				return SQL.ADOToString(UOX.dbRawAdo, "SELECT * FROM v_Trackers_Outlook ORDER BY [Count] DESC");
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		#endregion

		#region AirCept
		[WebMethod]//111V1082830
		public string AirCept_FetchHistory(string esn) {
			return AirCept_Fetch(esn, false);
		}
		[WebMethod]//111V1082830
		public string AirCept_FetchLocation(string esn) {
			return AirCept_Fetch(esn, true);
		}
		[WebMethod]//111V1082830
		public string AirCept_Fetch(string esn, bool locate) {
			try {
                throw new NotImplementedException();
                //if (HttpContext.Current.Cache[esn] != null) locate = false;
                //AirCept.DSs.RespDS dsResp = AirCept.Fetch.History(esn, locate, Config.AirCept.URL, Config.AirCept.UserName, Config.AirCept.Password);
                //if (locate && HttpContext.Current.Cache[esn] == null) {
                //    HttpContext.Current.Cache.Insert(esn, true, null, DateTime.Now.Add(new TimeSpan(0, Config.AirCept.LocationRequestInterval, 0)), TimeSpan.Zero, CacheItemPriority.Default, null);
                //    int actionRequestID = dsResp.action_request[0].id;
                //    DateTime startTime = DateTime.Now;
                //    while (dsResp.action.Select("id=" + actionRequestID)[0]["event_id"].ToString() == "") {
                //        if (startTime.Add(new TimeSpan(0, Config.AirCept.LocationRequestTimeout, 0)) < DateTime.Now) {
                //            dsResp.history_event.Addhistory_eventRow(DateTime.Now.ToString("yyyy/MM/dd HH:mm"), "<FONT color='DARKRED'>Error</FONT>", "<FONT color='DARKRED'>Timeout while locating tracker [" + esn + "]</FONT>", "0", "0", "666", esn, "-1");
                //            HttpContext.Current.Cache.Remove(esn);
                //            break;
                //        }
                //        dsResp = AirCept.Fetch.History(esn, false, Config.AirCept.URL, Config.AirCept.UserName, Config.AirCept.Password);
                //    }
                //}
                //AirCept.DSs.RespDS.history_eventDataTable dt = dsResp.history_event;
                //dsResp.Tables.Remove(dt);
                //return SQL.SortTable(dt, "Date DESC").GetXml();
			} catch (Exception exc) { throw new ClientException(exc); }
		}

		#region AirCept_FetchHistory OLD
		public string AirCept_FetchHistory_(string esn) {
			try {
				WebClient wc = new WebClient();
				ReqDSEx dsReq = new ReqDSEx();
				ReqDS.apiclientRow api = dsReq.apiclient.AddapiclientRow("david", "martha801", 0);
				dsReq.history_request.Addhistory_requestRow(DateTime.Now.Ticks + "", esn, api);
				string xml = new XmlDataDocument(dsReq).DocumentElement.InnerXml;
				byte[] response = wc.UploadData("https://www.wireless-security.com/API/apixml.asp", Encoding.ASCII.GetBytes(xml));
				RespDS dsResp = new RespDS();
				dsReq.EnforceConstraints = false;
				dsResp.ReadXml(new StringReader(Encoding.ASCII.GetString(response)));
				DataSet dsEvents = new DataSet();
				RespDS._eventDataTable dt = (RespDS._eventDataTable)dsResp._event.Clone();
				dsEvents.Tables.Add(dt);
				dsEvents.Merge(dsResp._event);
				foreach (RespDS._eventRow row in dt.Rows) {
					DateTime d = new DateTime(1970, 1, 1, 0, 0, 0) + new TimeSpan(row.utc * 10000000L);
					row.Date = d.ToLocalTime().ToString("yyyy/MM/dd hh:mm");
				}
				dt.Columns.Remove(dt.utcColumn);
				dt.Columns.Remove(dt.esnColumn);
				dt.Columns.Remove(dt.member_idColumn);
				dt.Columns.Remove(dt.accessunitsColumn);
				dt.Columns.Remove(dt.speedColumn);
				dt.idColumn.ColumnMapping = MappingType.Hidden;
				AirCeptDS dsAir = new AirCeptDS();
				SQL.ExecuteDatasetEx(db, dsAir.EventCode, "SELECT ID,Name FROM ts_AirCept_EventCode");
				foreach (RespDS._eventRow row in dt)
					try { row.EventName = dsAir.EventCode.Select("ID=" + row.eventcode)[0]["Name"] + ""; } catch { }
				dt.Columns.Remove(dt.eventcodeColumn);
				SQL.SortTable(dt, "Date DESC");
				return dsEvents.GetXml();
				//uox.FaxForm(html,fax==null?uox.LoanInfo.DealerFax:fax,uox.LoanInfo.DealerName,subject);
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		class ReqDSEx : ReqDS {
			public ReqDSEx()
				: base() {
				apiclient.apiclient_IdColumn.ColumnMapping = MappingType.Hidden;
				action_request.apiclient_IdColumn.ColumnMapping = MappingType.Hidden;
				history_request.apiclient_IdColumn.ColumnMapping = MappingType.Hidden;
			}
		}
		#endregion

		#endregion AirCept

		#region Megasys
		[WebMethod]
		public int ExportFromMegasys(string MsID) {
			try {
				return (int)SQL.ExecSP(db, "s_ExportFromMegasys", MsID);
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		[WebMethod]
		public string FindInMegasysEx(string filter) {
			try {
				if ((filter + "") == "") filter = "1=0";
				return SQL.ADOToString(1500, dbADO, 0, "SELECT * FROM v_ExportFromMegasys_List WHERE " + SQL.CheckInjects(filter.Replace("*", "%")));
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		[WebMethod]
		public string FindInMegasys(string fieldName, string fieldValue) {
			try {
				SQL.CheckInjects(fieldName);
				return SQL.XmlInnerText(db, "SELECT * FROM v_ExportFromMegasys WHERE [" + fieldName + "] LIKE @FN", "%" + fieldValue + "%");
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		#endregion

		#region Trackers

		#region Licenses
		[WebMethod]
		public string PurchaseLicenses(int branchID, int licenseCount, bool filter) {
			try {
				if (licenseCount != 0)
					SQL.ExecuteDatasetEx(db, "UPDATE t_Branch SET TrackerLicensePurchased = TrackerLicensePurchased + @LC WHERE ID=@BID", licenseCount, branchID);
				return uox.Tracker().TrackerLicensesXml(filter ? branchID : 0);
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		[WebMethod]
		public string PurchaseLicensesHist(int branchID) {
			try {
				return SQL.ExecuteDatasetEx(db, "SELECT *,CONVERT(varchar,[When],111) When_F FROM t_TrackerLicensesHistory WHERE BranchID=@BID ORDER BY ID DESC", branchID).GetXml();
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		#endregion

		#region TrackingDevice
		[WebMethod]
		public string TrackingDevice_Get(string filter) {
			try {
				SQL.CheckInjects(filter);
				if (filter == "" || filter == "0") filter = "1=0";
				return SQL.ADOToString(uox.dbADO, SQL.SQL_TRACKING_DEVICES + " WHERE " + filter);
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		[WebMethod]
		public void TrackingDevice_Update(string fieldName, string fieldValue, string id) {
			try {
				HashTableEx ht = new HashTableEx(fieldName, fieldValue);
				SQL.UpdateDataTable(ht, uox.db, SQL.SQL_TRACKING_DEVICES + " WHERE ID=@ID", id);
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		[WebMethod]
		public void TrackingDevice_Add(string[] ids) {
			try {
				string xml = "<t>" + string.Join("</t><t>", ids) + "</t>";
				SQL.ExecuteDatasetEx(uox.db, "s_Trackers_Add", xml);
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		[WebMethod]
		public string TrackerStatus(string trackerID, int statusID) {
			try {
				SQL.UpdateDataTable(new HashTableEx("StatusID", statusID), new UOX().db, "SELECT * FROM t_Tracker WHERE ID=@ID", trackerID);
				return UOX.TrackerClass.TrackerStatusList(trackerID).GetXml();
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		[WebMethod]
		public void Trackers_ShipBack(string trackerIDs, string shipmentID) {
			try {
				SQL.ExecuteNonQueryEx(db, "s_Tracker_WaitingForNew", trackerIDs, shipmentID);
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		[WebMethod]
		public void Trackers_InHouse(string trackerIDs, string shipmentID) {
			try {
				SQL.ExecuteNonQueryEx(db, "s_Tracker_InHouse", trackerIDs);
			} catch (Exception exc) { throw new ClientException(exc); }
		}

		#endregion Tracker Device

		#region TrackerTransaction
		[WebMethod]
		public string TrackerTransaction_Get(string filter) {
			try {
				return uox.Tracker().Transactions(filter);
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		[WebMethod]
		public void TrackerTransaction_Update(int id, string fieldName, string fieldValue) {
			try {
				SQL.ExecuteNonQueryEx(db,
					"UPDATE v_TrackerTransaction SET " + SQL.CheckInjects(fieldName) + "=@V WHERE ID=@ID", fieldValue, id);
			} catch (Exception exc) { throw new ClientException(exc); }
		}

		#endregion

		#region VIN Update
		[WebMethod]
		public string TrackerRequest_VIN(int requestID, string VIN, bool updateLoan) {
			try {
				int loanID = updateLoan ? uox.Tracker(requestID).LoanID : 0;
				SQL.ExecSP(uox.db, "s_TrackerRequest_VIN_Update", requestID, VIN, loanID);
				uox = new UOX();
				return TrackerRequestFullInfo(requestID);
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		[WebMethod]
		public int TrackerRequest_LoanID(int requestID) {
			try {
				return uox.Tracker(requestID).LoanID;
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		[WebMethod]
		public void Loan_VIN(string VIN, int trackerRequestID) {
			try {
				SQL.ExecSP(uox.db, "s_Loan_VIN_Update", uox.applID, VIN, trackerRequestID);
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		[WebMethod]
		public int Loan_TrackerRequestID(string VIN) {
			try {
				if ((VIN + "") == "") return 0;
				string sql = "SELECT TrackerRequestID FROM v_TrackerRequest WHERE VIN = @VIN AND DealerID <> @DID AND IsActive = 1";
				ArrayListEx al = SQLHelperEx.ExecuteScalarArray(uox.db, sql, VIN, uox.LoanInfo.DealerID);
				if (al.Count > 1) throw new Exception("Multiple active tracker requests exist for VIN " + VIN);
				return al.Count > 0 ? (int)al[0] : 0;
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		#endregion

		#region TrackerRequest
		[WebMethod]
		public string TrackerRequest_Note(int trackerRequestID, string note) {
			try {
				return uox.Tracker(trackerRequestID).RequestNote(note).GetXml();
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		[WebMethod]
		public string TrackerRequest_Update(string fieldName, string fieldValue, int requestID) {
			try {
				if (requestID == 0)
					requestID = uox.Tracker(true).TrackerRequestID;
				SQL.UpdateField(db, "SELECT * FROM t_TrackerRequest WHERE ID=" + requestID, fieldName, fieldValue);
				return TrackerRequestFullInfo(requestID);
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		public string TrackerRequestFullInfo(int requestID) {
			UOX.TrackerClass tracker = uox.Tracker(requestID);
			return SQL.JoinTablesExXml(
				tracker.RequestStatusList(), "Statuses",
				tracker.DS(), "TrackerRequest",
				tracker.Technician_Trackers(), "TechTrackers",
				tracker.Trackers(), "Trackers",
				UOX.TrackerClass.Trackers(tracker.VIN), "TrackersByVIN",
				AdoStringDS(tracker.Technician_Requests()), "TechRequests"
				);
		}
		private DataSet AdoStringDS(string adoString) {
			DataTable dt = new DataSet().Tables.Add();
			dt.TableName = "AdoTable";
			dt.Columns.Add("AdoString");
			dt.Rows.Add(new object[] { adoString });
			return dt.DataSet;
		}
		[WebMethod]
		public string TrackerRequest_History(int requestID) {
			try {
				return uox.Tracker(requestID).RequestHistory().GetXml();
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		[WebMethod]
		public string TrackerRequest_Statuses(int requestID) {
			try {
				return uox.Tracker(requestID).RequestStatusList().GetXml();
				//,UOX.TrackerClass.TrackersByVIN(vin),
				//	"Statuses","Trackers");
				//return SQL.JoinTables(ds,UOX.TrackerClass.Technician_Trackers(technicianID),"","TechTrackers").GetXml();
				//return SQL.JoinTables(ds,uox.LoanInfo.DS(),"","LoanInfo").GetXml();
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		[WebMethod]
		public string Trackers(int requestID) {
			try {
				UOX.TrackerClass tracker = uox.Tracker(requestID);
				return SQL.JoinTablesExXml(
					tracker.Trackers(), "Trackers",
					tracker.TrackersByVIN(), "TrackersByVIN");
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		#endregion

		#region Trackers By ...
		[WebMethod]
		public string TrackersByLoan() {
			try {
				return uox.Tracker().TrackersByLoan().GetXml();
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		[WebMethod]
		public string TrackersByVIN(string vin) {
			try {
				return UOX.TrackerClass.Trackers(vin).GetXml();
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		#endregion

		#region Technician - Tracker(Request)
		[WebMethod]
		public string TechnicianTracker_InHands(int technicianID, string trackerID) {
			try {
				if (trackerID + "" == "") return uox.Tracker().Technician_Trackers(technicianID).GetXml();
				UOX.TrackerClass.Technician_Tracker_InHands(technicianID, trackerID);
				return TechnicianTrackers(technicianID);
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		[WebMethod]
		public string TechnicianTracker_Cancel(int technicianID, string trackerID) {
			try {
				UOX.TrackerClass.Tracker_InHouse(trackerID);
				return TechnicianTrackers(technicianID);
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		[WebMethod]
		public string Technician_Requests(int technicianID) {
			try {
				return uox.Tracker().Technician_Requests(technicianID);
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		[WebMethod]
		public string TechnicianTrackers(int technicianID) {
			try {
				DataSet ds = SQL.JoinTables(uox.Tracker().Technician_Trackers(technicianID), UOX.TrackerClass.Trackers_InHouse(), "TechTrackers", "Trackers");
				ds = SQL.JoinTables(ds, UOX.TrackerClass.Technician_Cars(technicianID), "", "Cars");
				return SQL.JoinTables(ds, uox.Tracker().Technician(technicianID), "", "Technician").GetXml();
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		[WebMethod]
		public string TechnicianInfo(int technicianID) {
			try {
				return uox.Tracker().Technician(technicianID).GetXml();
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		#endregion

		#region TrackerCar
		[WebMethod]
		public string TrackerCar_Installed(int trackerRequestID, string trackerIDs) {
			try {
				//int technicianID = (int)SQL.ExecuteScalarEx(db,"SELECT TechnicianID FROM t_Technician_Tracker WHERE TrackerID=@TrackerID",trackerID.Split(',')[0]);
				SQL.ExecuteNonQueryEx(db, "s_Tracker_Car_Install", trackerRequestID, trackerIDs);
				return TrackerRequestFullInfo(trackerRequestID);//TrackerRequest_Statuses();// SQL.JoinTables(uox.Tracker.Technician_Trackers(technicianID),uox.Tracker.TrackersByLoan(),"TechTrackers","LoanTrackers").GetXml();
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		#endregion TrackerCar

		#endregion

		#region Applicant Info
		[WebMethod]
		public string Borrower_Update(string fieldName, string fieldValue, int borrowerID, int loanID) {
			try {
				if (UOX.HasPageSessionID) uox.LockApplicant();
				if (borrowerID == 0) borrowerID = (int)SQL.ExecSP(db, "s_Borrower_Add", applID, -1);
				bool delete = borrowerID < 0;
				borrowerID = Math.Abs(borrowerID);
				System.Linq.Expressions.Expression<Func<t_Loan_Borrower,bool>> cond = s => s.LoanID == loanID && s.BorrowerID == borrowerID;
				if (delete) new MotoBank().t_Loan_Borrowers.Delete(cond);
				else new BorrowersDataSource().UpdateField(new OrderedDictionaryEx("ID", borrowerID), fieldName, fieldValue, false);
				XLinq.XElement xe = new System.Xml.Linq.XElement("DataSet",
					new XLinq.XElement("Dummy", new XLinq.XAttribute("attr", "empty")),
					new MotoBank().t_Loan_Borrowers.Where(cond).ToXML().Descendants()
				);
				return xe.ToString();
			} catch (Exception exp) { throw new ClientException(exp); }
		}
		[WebMethod]
		public string Address_Update(string fieldName, string fieldValue, int addressID) {
			try {
				if (UOX.HasPageSessionID) uox.LockApplicant();
				return new MotoBank().t_Addresses
					.Update(a => a.ID == addressID, new Dictionary<string, object>() { { fieldName, fieldValue } }).ToXML("Address").ToString();
				//SQL.UpdateField(uox.db, "SELECT * FROM t_Address WHERE ID =" + addressID, fieldName, fieldValue);
			} catch (Exception exp) { throw new ClientException(exp); }
		}
		[WebMethod]
		public string StreetByZipAndHouseNumber(string zip, string houseNumber) {
			try {
				return Dimok.Web.HTTP.StreetByZipAndHouseNumber(zip, houseNumber.Trim()).GetXml();
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		[WebMethod]
		public void Employment_Update(string fieldName, string fieldValue, int employmentID) {
			try {
				if (UOX.HasPageSessionID) uox.LockApplicant();
				SQL.UpdateField(uox.db, "SELECT * FROM t_Employment WHERE ID =" + employmentID, fieldName, fieldValue);
			} catch (Exception exp) { throw new ClientException(exp); }
		}
		public int xmlApplInfo(string fieldName, string fieldValue) {
			try {
				if (UOX.HasPageSessionID) uox.LockApplicant();
				SQL.UpdateField(uox.db, "SELECT * FROM t_Borrower WHERE ID =" + uox.ApplInfo.BorrowerID(0), fieldName, fieldValue);
				return 0;
			} catch (Exception exp) { throw new ClientException(exp, "Field", fieldName, "Value", fieldValue); }
		}
		[WebMethod]
		public string xmlApplInfoCo(string fieldName, string fieldValue) {
			try {
				if (UOX.HasPageSessionID) uox.LockApplicant();
				SQL.ExecuteNonQueryEx(db, "s_Borrower_Add", applID, 1);
				SQL.UpdateField(uox.db, "SELECT * FROM t_Borrower WHERE ID =" + uox.ApplInfo.BorrowerID(1), fieldName, fieldValue);
				if (Regex.IsMatch(fieldName, "(firstname|SSN)", RegexOptions.IgnoreCase) && fieldValue != null && fieldValue != "")
					return SQL.ExecuteScalarEx(uox.db, "SELECT ISNULL(LastName,'') LastName FROM Borrower(" + uox.applID + ",1)") + "";
				return "";
			} catch (Exception exp) { throw new ClientException(exp); }
		}
		[WebMethod]
		public string XmlReferences(string fieldName, string fieldValue, string id, int borrowerID) {
			try {
				if (UOX.HasPageSessionID) uox.LockApplicant();
				if (!Dimok.Common.IsNumeric(id) || int.Parse(id) == 0) {
					SQL.CheckInjects(fieldName);
					SQL.ExecuteDatasetEx(uox.db, "INSERT INTO t_References(BorrowerID," + fieldName + ")VALUES(@BID,@Value)", borrowerID, fieldValue);
				} else
					SQL.UpdateField(uox.db, "SELECT * FROM t_References WHERE ID=" + id, fieldName, fieldValue);
				return uox.ApplInfo.ReferencesXml(borrowerID);
			} catch (Exception exp) { throw new ClientException(exp); }
		}
		[WebMethod]
		public string XmlInterviews(string fieldName, string fieldValue, string id, int borrowerID) {
			try {
				if (UOX.HasPageSessionID) uox.LockApplicant();
				if (!Dimok.Common.IsNumeric(id) || int.Parse(id) == 0) {
					SQL.CheckInjects(fieldName);
					SQL.ExecuteDatasetEx(uox.db, "INSERT INTO t_Interviews(BorrowerID," + fieldName + ")VALUES(@BID,@Value)", borrowerID, fieldValue);
				} else
					SQL.UpdateField(uox.db, "SELECT * FROM t_Interviews WHERE ID=" + id, fieldName, fieldValue);
				return uox.ApplInfo.InterviewsXml(borrowerID);
			} catch (Exception exp) { throw new ClientException(exp); }
		}
		#endregion

		#region Loan Info

		#region Status Update
		[WebMethod]
		public string LoanStatus(int stateTo, int stateFrom) {
			try {
				if (stateFrom != new UOX().LoanInfo.StatusID) throw new Exception("Status has been change since page loaded.\n\nRefresh your browser.");
				return SQL.JoinTables(stateTo == 0 ? uox.LoanInfo.NextStatuses().Table.DataSet: uox.LoanInfo.SetStatus(stateTo).Table.DataSet, uox.LoanInfo.DS(true)).GetXml();
			} catch (Exception exc) {
				DataSet ds = new DataSet();
				try {
					ds = SQL.JoinTables(uox.LoanInfo.NextStatuses().Table.DataSet, uox.LoanInfo.DS(true));
				} catch (Exception dsExc) {
					throw new ClientException(exc, "DS Error", dsExc.Message);
				}
				throw new ClientException(ds, exc);
			}
		}
		[WebMethod]
		public string VerificationStatus(int stateTo, int stateFrom) {
			try {
				if (stateFrom != uox.Verification.StatusID) throw new Exception("Status has been change since page loaded.\n\nRefresh your browser.");
				return SQL.JoinTables(stateTo == 0 ? uox.Verification.NextStatuses().DataSet: uox.Verification.SetStatus(stateTo).DataSet, uox.LoanInfo.DS(true)).GetXml();
			} catch (Exception exc) {
				DataSet ds = new DataSet();
				try {
					ds = SQL.JoinTables(uox.Verification.NextStatuses().DataSet, uox.LoanInfo.DS(true));
				} catch (Exception dsExc) {
					throw new ClientException(exc, "DS Error", dsExc.Message);
				}
				throw new ClientException(ds, exc);
			}
		}
		[WebMethod]
		public string CollectionStatus(int stateTo, int stateFrom, int applID) {
			try {
				if (applID > 0) uox.applID = applID;
				int statusCurr = uox.Collection.StatusID;
				//if (statusCurr > 0 && stateFrom != uox.Collection.StatusID) throw new Exception("Status has been change since page loaded.\n\nRefresh your browser.");
				return SQL.JoinTables((stateTo == 0 ? uox.Collection.NextStatuses(uox.LoanInfo.Row["StatusID_Collection"]) : uox.Collection.SetStatus(stateTo)).DataSet, uox.LoanInfo.DS(true)).GetXml().Replace(" xml:space=\"preserve\"", "");
			} catch (Exception exc) {
				DataSet ds = new DataSet();
				try {
					ds = SQL.JoinTables(uox.Collection.NextStatuses().DataSet, uox.LoanInfo.DS(true));
				} catch (Exception dsExc) {
					throw new ClientException(exc, "DS Error", dsExc.Message);
				}
				throw new ClientException(ds, exc);
			}
		}
		[WebMethod]
		public string BuyBackStatus(int stateTo, int stateFrom, int applID) {
			try {
				if (applID > 0) uox.applID = applID;
				int statusCurr = uox.BuyBack.StatusID;
				//if (statusCurr > 0 && stateFrom != uox.BuyBack.StatusID) throw new Exception("Status has been change since page loaded.\n\nRefresh your browser.");
				return SQL.JoinTables((stateTo == 0 ? uox.BuyBack.NextStatuses(uox.LoanInfo.Row["StatusID_BuyBack"]) : uox.BuyBack.SetStatus(stateTo)).DataSet, uox.LoanInfo.DS(true)).GetXml();
			} catch (Exception exc) {
				DataSet ds = new DataSet();
				try {
					ds = SQL.JoinTables(uox.BuyBack.NextStatuses().DataSet, uox.LoanInfo.DS(true));
				} catch (Exception dsExc) {
					throw new ClientException(exc, "DS Error", dsExc.Message);
				}
				throw new ClientException(ds, exc);
			}
		}
		[WebMethod]
		public string BKStatus(int stateTo, int stateFrom, int applID) {
			try {
				if (applID > 0) uox.applID = applID;
				int statusCurr = uox.Bankruptcy.StatusID;
				//if (statusCurr > 0 && stateFrom != uox.Bankruptcy.StatusID) throw new Exception("Status has been change since page loaded.\n\nRefresh your browser.");
				return SQL.JoinTables((stateTo == 0 ? uox.Bankruptcy.NextStatuses(uox.LoanInfo.Row["StatusID_BK"]) : uox.Bankruptcy.SetStatus(stateTo)).DataSet, uox.LoanInfo.DS(true)).GetXml();
			} catch (Exception exc) {
				DataSet ds = new DataSet();
				try {
					ds = SQL.JoinTables(uox.Bankruptcy.NextStatuses().DataSet, uox.LoanInfo.DS(true));
				} catch (Exception dsExc) {
					throw new ClientException(exc, "DS Error", dsExc.Message);
				}
				throw new ClientException(ds, exc);
			}
		}
		[WebMethod]
		public string RepoStatus(int stateTo, int stateFrom, int applID) {
			try {
				if (applID > 0) uox.applID = applID;
				int statusCurr = uox.Repo.StatusID;
				//if (statusCurr > 0 && stateFrom != uox.Repo.StatusID) throw new Exception("Status has been change since page loaded.\n\nRefresh your browser.");
				return SQL.JoinTables((stateTo == 0 ? uox.Repo.NextStatuses() : uox.Repo.SetStatus(stateTo)).DataSet, uox.LoanInfo.DS(true)).GetXml();
			} catch (Exception exc) {
				DataSet ds = new DataSet();
				try {
					ds = SQL.JoinTables(uox.Repo.NextStatuses().DataSet, uox.LoanInfo.DS(true));
				} catch (Exception dsExc) {
					throw new ClientException(exc, "DS Error", dsExc.Message);
				}
				throw new ClientException(ds, exc);
			}
		}
		#endregion

		[WebMethod]
		public string XmlLoanInfoTIL(string fieldName, string fieldValue) { return XmlLoanInfo(fieldName, fieldValue); }

		#region XmlLoanInfo
		[WebMethod]
		public string XmlLoanInfo(string fieldName, string fieldValue) {
			try {
				if ((fieldName + "") != "") {
					if (UOX.HasPageSessionID) uox.LockApplicant();
					uox.LoanInfo.Update(fieldName, fieldValue);
				}
				return uox.LoanInfo.GetXml();
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		#endregion

		[WebMethod]
		public string PaymentsSchedule() {
			return uox.LoanInfo.PaymentsSchedule().GetXml();
		}

		#region TIL Snapshot
		[WebMethod]
		public string xmlTIL_Snapshot(string fieldName, string fieldValue, string snapshotName) {
			try {
				var mb = new MotoBank();
				if ((fieldName + "") != "")
					mb.v_TruthInLending_Snapshots.Update(applID, snapshotName, new Dictionary<string,object>(){{fieldName, fieldValue}});
				var ssList = mb.t_LoanInfo_Snapshots.Where(s => s.LoanID == applID).DefaultIfEmpty().Select(s => new { s.LoanID, s.SnapshotName });
				if (snapshotName == "" && ssList.Count() > 0)
					snapshotName = ssList.ToArray()[0].SnapshotName;
				var ds = ssList.GetDataSet(mb, "SnapshotsList");
				mb.v_TruthInLending_Snapshots.DefaultIfEmpty().Where(s => s.LoanID == applID && new[] { s.SnapshotName, "" }.Contains(snapshotName)).GetDataSet(mb, ds);
				return ds.GetXml().Replace(" xml:space=\"preserve\"", "");
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		[WebMethod]
		public string xmlTIL_Snapshot_Add(string snapshotName) {
			try {
				var mb = new MotoBank();
				mb.t_LoanInfo_Snapshots.Insert(new Dictionary<string, object>() { { "LoanID", applID }, { "SnapshotName", snapshotName } });
				return xmlTIL_Snapshot("", "", snapshotName);
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		[WebMethod]
		public string xmlTIL_Snapshot_Remove(string snapshotName) {
			try {
				var mb = new MotoBank();
				mb.t_LoanInfo_Snapshots.DeleteOnSubmit(mb.t_LoanInfo_Snapshots.Single(s => s.LoanID == applID && s.SnapshotName == snapshotName));
				mb.SubmitChanges();
				return xmlTIL_Snapshot("", "", "");
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		#endregion

		#endregion Loan Info

		#region Purchase Contracy
		[WebMethod]
		public void RemoveFromBatch() {
			try {
				new PurchaseBatch_LoansDataSource().DeleteByLoanID(applID);
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		[WebMethod]
		public string XmlPurchaseContract(string fieldName, string fieldValue) {
			try {
				PurchaseContractsDataSource dsPC = new PurchaseContractsDataSource();
				if ((fieldName + "") == "")
					return SQL.XmlInnerText(dsPC.SelectEx(applID).Table);
				return dsPC.UpdateFields(new OrderedDictionaryEx(fieldName, fieldValue, "LoanID", uox.applID), uox.LoanInfo.Keys).DataSet.GetXml();
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		[WebMethod]
		public string XmlDecisionFaults() {
			try {
				var ret = new MotoBank().GL_ShowDecisionFaults(uox.applID).OrderBy(r => r.PathIndex)
					.Select(r=>new {ImgSrc="/images/buttons/Delete.gif",Text=r.Text}).ToList();
				if (ret.Count() == 0) ret.Add(new { ImgSrc = "/images/money_dollar.png", Text = "APPROVED" });
				return ret.ToXML().ToString();
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		[WebMethod]
		public GL_ProgramInfoByLoanResult GL_ProgramIDByLoan() {
			try {
				return new MotoBank().GL_ProgramInfoByLoan(uox.applID).Single();
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		//
		#endregion

		#region Fetch Black Book ************************
		private static Manheim.ManheimServer _mmr = null;
		private Manheim.ManheimServer MMR {
			get {
				//if( this.Context.Cache["MMR"] != null )return (Manheim.ManheimServer)this.Context.Cache["MMR"];
				//HttpContext.Current.Cache["MMR"] = 
				if( _mmr == null )_mmr = Manheim.ManheimClient.Activate();
				return _mmr;
			}
		}

		#region IBlackBook Members

		[WebMethod]
		public string[] FetchSeries(int year, string make, string model,string providerCode) {
			providerCode = providerCode.Default("B");
			List<string> series = new List<string>();
			switch (providerCode) {
				case "N":
					series.AddRange(Dimok.NADA.Fetch.Series(year, make, model)); break;
				case "B":
					series.AddRange(new BlackBook.BlackBook_WS().FetchSeries(year, make, model)); break;
				case "M":
					return new string[] { };
				default: throw new NotImplementedException(ConfigLocal.BookValue.Provider + " is not implemented.");
			}
			if( series.Count > 1) series.Insert(0, "");
			return series.ToArray();
		}

		[WebMethod]
		public string[] FetchBodyStyles(int year, string make, string model, string series, string providerCode) {
			providerCode = providerCode.Default("B");
			List<string> body = new List<string>();
			switch (providerCode) {
				case "N":
					body.AddRange(Dimok.NADA.Fetch.BodyStyle(year, make, model, series)); break;
				case "B":
					body.AddRange(new BlackBook.BlackBook_WS().FetchBodyStyles(year, make, model, series)); break;
				case "M":
					lock (_mmr) {
						body.AddRange(MMR.GetStyles(year + "", make, model)); break;
					}
				default: throw new NotImplementedException(ConfigLocal.BookValue.Provider + " is not implemented.");
			}
			if (body.Count > 1) body.Insert(0, "");
			return body.ToArray();
		}

		[WebMethod]
		public string[] FetchModels(int year, string make, string providerCode) {
			providerCode = providerCode.Default("B");
			List<string> models = new List<string>();
			switch (providerCode) {
				case "N":
					models.AddRange(Dimok.NADA.Fetch.Models(year, make)); break;
				case "B":
					models.AddRange(new BlackBook.BlackBook_WS().FetchModels(year, make)); break;
				case "M":
					lock (_mmr) {
						models.AddRange(MMR.GetModels(year + "", make)); break;
					}
				default: throw new NotImplementedException(ConfigLocal.BookValue.Provider + " is not implemented.");
			}
			if (models.Count > 1) models.Insert(0, "");
			return models.ToArray();
		}

		#endregion

		[WebMethod]
		public string[] FetchYears() {
			return SQLHelperEx.ExecuteScalarArray(UOX.dbRaw, "SELECT DISTINCT Year FROM t_Car_YearMake ORDER BY Year DESC");
		}
		[WebMethod]
		public string[] FetchMakes(int year, string providerCode) {
			providerCode = providerCode.Default("B");
			List<string> makes = new List<string>();
			switch (providerCode) {
				case "N":
					makes.AddRange(Dimok.NADA.Fetch.Makes(year));
					break;
				case "B":
					makes.AddRange(SQLHelperEx.ExecuteScalarArray(UOX.dbRaw, "SELECT DISTINCT Make FROM t_Car_YearMake WHERE Year=@Year ORDER By Make", year).Cast<string>());
					break;
				case "M":
					lock (_mmr) {
						makes.AddRange(MMR.GetMakes(year + ""));
						break;
					}
				default: throw new NotImplementedException(ConfigLocal.BookValue.Provider + " is not implemented.");
			}
			makes.Insert(0, "");
			return makes.ToArray();
		}
		[WebMethod]
		public string FetchVIN(string vin, string state, int mileage, string providerCode) {
            var ucWS = new Dimok.BlackBook.UsedCarWS.UsedCarWSClient();
            var cred = new Dimok.BlackBook.UsedCarWS.UserCredentials { userid = "LendStrong", password = "bb030512XFq", producttype = "W", customer = "XXX1234" };
            var vinValues = ucWS.getVINValues(ref cred, "U", "W", vin, "", 0, "FL", 0, 0, 0, 0, false, false, true);

			providerCode = providerCode.Default("B");
			PriceInfoDS dsPI = new PriceInfoDS();
			string vinFetch = vin.Length == 10 ? vin.Substring(0, 8) + vin.Substring(9, 1) : vin;
			try {
				if (vin.Length != 17 && vin.Length != 9) throw new Exception("VIN must have 17,10 or 9 positions.");
				switch (providerCode) {
					case "N":
						dsPI = Dimok.NADA.Fetch.BookValue(vin, mileage, state);
						LoadMMR(dsPI, null);
						break;
					case "B":
						vinFetch = vinFetch.Split('_')[0];
						dsPI = BlackBook.BlackBook.LoadPriceInfo(vinFetch, state, mileage);
						LoadMMR(dsPI, null);
						break;
					case "M":
						var ci = uox.BB.PriceInfo(vin).CarInfo[0];
						lock (MMR) {
							Manheim.ManheimPriceDS dsMMR = MMR.GetPriceByVIN("NA", vinFetch, mileage, ci.Make + " " + ci.Model + " " + ci.Series + " " + ci.Body_Style);
							if( (ci.Make+"").Trim() == ""){
								var descriptions = dsMMR.Price[0].Description.Split(',');
								ci.Year = descriptions[0];
								ci.Make = descriptions[1];
								ci.Model = descriptions[2];
								ci.Series = "";
								ci.Body_Style = descriptions[3];
							}
							dsPI.CarInfo.ImportRow(ci);
							LoadMMR(dsPI, dsMMR);
						}
						break;
				}
				if (dsPI.CarInfo.Count == 0) throw new Exception("No car found for this VIN.");
				if (dsPI.CarInfo.Count > 1) return SQL.XmlInnerText(dsPI);
				PriceInfoDS.CarInfoRow drCI = dsPI.CarInfo[0];
				drCI.VIN = vin;
				uox.BB.SaveCarInfo(dsPI);
				return GetCarInfo(vin);
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		[WebMethod]
		public string FetchPriceInfo(string vin, string year, string make, string model, string series, string bodyStyle, string[] addOns, string state, int mileage,string providerCode) {
			providerCode = providerCode.Default("B");
			PriceInfoDS dsPI = new PriceInfoDS();
			try {
				switch (providerCode) {
					case "N":
						dsPI = Dimok.NADA.Fetch.BookValue(int.Parse(year), make, model, series, bodyStyle, mileage, state);
						LoadMMR(dsPI, null);
						break;
					case "B":
						if ((year + "") != "" && (make + "") != "" && (model + "") != "" && (series + bodyStyle + "") != "")
							dsPI = BlackBook.BlackBook.LoadPriceInfo(year, make, model, series, bodyStyle, addOns, state, mileage);
						LoadMMR(dsPI, null);
						break;
					case "M":
						dsPI = new PriceInfoDS();
						dsPI.CarInfo.AddCarInfoRow("", "", year + "", make + "", model + "", series + "", bodyStyle + "", mileage, state, 0, "", "","M");
						lock (_mmr) {
							var dsMMR = MMR.GetPrice(year, make, model, bodyStyle, mileage);
							LoadMMR(dsPI, dsMMR);
						}
						break;
				}
				if (dsPI.CarInfo.Count > 1) return SQL.XmlInnerText(dsPI);
				if (dsPI.CarInfo.Count == 0) {
					dsPI.CarInfo.AddCarInfoRow("", "", year + "", make + "", model + "", series + "", bodyStyle + "", mileage, state, 0, "", "",providerCode);
				}
				if (vin.Length < 9 || vin.Length != 17 && vin.Substring(0, 9) != dsPI.CarInfo[0].VIN_9) vin = "";
				if (vin != "") dsPI.CarInfo[0].VIN = vin;
				if (dsPI.CarInfo[0].VIN == "") dsPI.CarInfo[0].VIN = MakeVin(year, make, model, series, bodyStyle);
				if (dsPI.CarInfo[0].VIN.Length < 16)
					dsPI.CarInfo[0].VIN += "".PadRight(10 - dsPI.CarInfo[0].VIN.Length, '_') + DateTime.Now.ToString("ffffff");
				uox.BB.SaveCarInfo(dsPI);
				return GetCarInfo(dsPI.CarInfo[0].VIN);
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		private void LoadMMR(PriceInfoDS dsPI, Manheim.ManheimPriceDS dsMMR) {
			if (dsMMR == null || dsMMR.Price.Count == 0) return;
			var carInfoRow = dsPI.CarInfo[0];
			foreach (Manheim.ManheimPriceDS.PriceRow row in dsMMR.Price)
				dsPI.BookValue.AddBookValueRow(row.Price,row.Type+"", "M", "", 0,row.Mileage);
			carInfoRow.DescriptionMMR = dsMMR.Price[0].Description;
			carInfoRow.MMRInfo = dsMMR.Price[0].Info;
			carInfoRow.BookValueProviderCode = dsPI.BookValue[0].Provider;
		}
		private string MakeVin(string year, params string[] parts) {
			string s = year.Substring(2, 2);
			foreach (string part in parts)
				s += MakeVinPosition(part + "");
			return s;
		}
		private string MakeVinPosition(string part) {
			string digits = Regex.Match(part, "\\d+").Value;
			return (part + "") == "" ? "" : part.Substring(0, 1) + (digits == "" ? part.Replace(" ", "").Length.ToString("X") : digits);
		}
		[WebMethod]
		public string GetCarInfo(string vin) {
			try {
				return uox.BB.PriceInfoXml(vin);
			} catch (Exception exp) { throw new ClientException(exp); }
		}
		[WebMethod]
		public string GetApplCarInfo() {
			try {
				throw new Exception("GetApplCarInfo should not be used!");
				//UOX uox = new UOX();
				//PriceInfoDS dsPriceInfo = new PriceInfoDS();
				//if( !uox.hasApplID )return dsPriceInfo.GetXml();
				//SQLHelperEx.ExecuteDatasetEx(uox.db,dsPriceInfo.CarInfo,uox.CarInfo.SQL_V);
				//if( dsPriceInfo.CarInfo.Count == 0 )throw new ASPException("No car info found for applicant "+uox.applID+".");
				//SQLHelperEx.ExecuteDatasetEx(uox.db,dsPriceInfo.AddOns,"SELECT * FROM v_Car_AddOn WHERE "+uox.ApplicantFilter());
				//return SQL.XmlInnerText(dsPriceInfo);
			} catch (Exception exp) { throw new ClientException(exp); }
		}
		#endregion Fetch ************************

		#region CarInfo
		[WebMethod]
		public string XmlCarInfo(string fieldName, string fieldValue) {
			try {
				if ((fieldName + "") != "") uox.CarInfo.Update(fieldName, fieldValue);
				return uox.CarInfo.XmlInnerText();
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		[WebMethod]
		public void Car(string fieldName, string fieldValue, string vin) {
			try {
				uox.BB.Update(fieldName, fieldValue, vin);
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		private void CarInfo(string fieldName, string fieldValue) {
			try {
				uox.CarInfo.Update(fieldName, fieldValue);
				if (fieldName.ToUpper() != "VIN" || fieldValue.Length != 17) return;
				uox.BB.SaveCarInfo(fieldValue,null);//, new UOX.BlackBookInner.DoPriceInfoDS(FetchMMR));
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		private PriceInfoDS FetchMMR(PriceInfoDS dsPI) {
			try {
				PriceInfoDS.CarInfoRow rowCI = dsPI.CarInfo[0];
				string vin = rowCI.VIN;
				if (vin.Length != 17) vin = rowCI.VIN_9;
				if (vin != "") {
					try {
						lock (_mmr) {
							var dsMMR = MMR.GetPriceByVIN("NA", vin, rowCI.Mileage, rowCI.Make + " " + rowCI.Model + " " + rowCI.Series + " " + rowCI.Body_Style);
							LoadMMR(dsPI, dsMMR);
						}
					} catch (Exception exc) {
						ClientException.Publish(exc, true);
						LoadMMR(dsPI, null);
					}
					//dsPI.BookValue.AddBookValueRow(MMRPrice,"MMR","",0);
				}
			} catch (Exception exc) { ClientException.Publish(exc, true); }
			return dsPI;
		}
		#endregion

		#region Get Time
		[WebMethod]
		public string GetTime(string server, int port) {
			try {
                throw new NotImplementedException();
                //return Dimok.Time.Daytime.GetTime().ToString("MMM dd hh:mm tt zzzz");
			} catch (Exception exc) {
				throw new ClientException(exc, "Server", server, "Port", port + "");
			}
		}
		#endregion

		#region Notes
		[WebMethod]
		public string FetchNoteDateDue(int statusID) {
			try {
				DateTime dateDue = (DateTime)SQL.ExecuteScalar("SELECT dbo.NoteDateDue(@StatusID)", statusID);
				return dateDue.ToString("MM/dd/yy hh:mm tt");
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		[WebMethod]
		public string FetchNoteActionCodes(int noteTypeID) {
			try {
				Note_Type_ActionCodesDataSource ds = new Note_Type_ActionCodesDataSource();
				ds.OrderBys = "IsTask,ActionLabel";
				DataTable dt = ((DataView)ds.SelectEx(noteTypeID)).Table;
				DataRow dr = dt.NewRow();
				dr["ActionLabel"] = "";
				dr["ActionCodeID"] = "0";
				dt.Rows.InsertAt(dr, 0);
				return dt.DataSet.GetXml();
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		[WebMethod]
		public string FetchNoteResultCodes(int noteActionCodeID) {
			try {
				DataTable dt = ((DataView)new NoteActionCode_StatusesDataSource(noteActionCodeID).Select()).Table;
				return dt.DataSet.GetXml();
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		[WebMethod]
		public string GetNote(string NoteID) {
			try {
				return new MotoBank().t_Notes.Single(s => s.ID + "" == NoteID).Note;
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		#endregion

		#region AltiGen
		[WebMethod]
		public void OnHook(string Extention, string Password) {
			try {
				ProfileCommon p = Profiler.GetProfile();
				if (Extention != "") p.IPPhone.Extention = Extention; else Extention = p.IPPhone.Extention;
				if (Password != "") p.IPPhone.Password = Password; else Password = p.IPPhone.Password;
				p.Save();
				//if (!Regex.IsMatch(Extention, "^\\d{4}$")) throw new Exception("Extention must be four digits.");
				new Dialer(Extention, Password).OnHook();
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		[WebMethod]
		public string Dial(string Extention, string Password, string PhoneNumber) {
			try {
				ProfileCommon p = Profiler.GetProfile();
				if (Extention != "") p.IPPhone.Extention = Extention; else Extention = p.IPPhone.Extention;
				if (Password != "") p.IPPhone.Password = Password; else Password = p.IPPhone.Password;
				p.Save();
				SQL.ExecuteNonQuery("UPDATE t_ADS_Login SET Extension = @Ext WHERE sAMAccountName = @SAM AND ISNULL(Extension,'')<>@Ext", Extention, Regex.Match(this.Context.User.Identity.Name, @"[^\\]+$").Value);
				//if (!Regex.IsMatch(Extention, "^\\d{4}$")) throw new Exception("Extention must be four digits.");
				//if (!Regex.IsMatch(Password, "^\\d{4,5}$")) throw new Exception("Password must be four or five digits.");
				PhoneNumber = Regex.Replace(PhoneNumber, "[^0-9]", "").Trim();
				if (!Regex.IsMatch(PhoneNumber, "^(\\d{4}|\\d{10,})$")) throw new Exception("Phone must have four or 10+ digits.");
				try {
					new Dialer(Extention, Password, PhoneNumber).Dial();
				} catch (Dialer.OnHookException exc) {
					return exc.Message;
				}
			} catch (Exception exc) { throw new ClientException(exc); }
			return "";
		}
		#endregion

		[WebMethod]
		public string GetVINsToGo(string vin) {
			try {
				DataTable dt = SQL.GetVINsToGo(db, "%" + vin + "%").Tables[0];
				if (dt.Rows.Count > 500 || dt.Rows.Count == 0) {
					dt.Rows.Clear();
					return SQL.XmlInnerText(dt, true);
				}
				return SQL.XmlInnerText(dt);
			} catch (Exception exc) { throw new ClientException(exc); }
		}

		[WebMethod]
		public void Fax(string html, string fax, string subject) {
			try {
				uox.FaxForm(html, fax == null ? uox.LoanInfo.DealerFax : fax, uox.LoanInfo.DealerName, subject);
			} catch (Exception exc) { throw new ClientException(exc); }
		}

		[WebMethod]
		public void PullCredit() {
			try {
				//try{
				byte bc = uox.LoanInfo.BorrowersCount();
				while (bc-- > 0) {
					SQL.TestUpdate.CreditInfo(uox.ApplInfo.BorrowerID(bc));
					uox.PullCredit(bc, CreditBureau.Equifax);
				}
				//}catch(CreditAlreadyExistsException exc){
				//	throw new ClientException(exc.Message);
				//}catch(CreditReportException exc){
				//	throw new ClientException(exc.Message);
				//}catch(Credit.Objects.CreditInquiryException exc){
				//	throw new ClientException(exc.Message);
				//}
			} catch (Exception exc) { throw new ClientException(exc); }
		}

		[WebMethod]
		public string GetColumnHistory(string pk_1, string pk_2, string pk_3, string tableName, string columnName) {
			try {
				if ((pk_1 + "") == "") pk_1 = uox.applID + "";
				return GetColumnHistoryDataSet(pk_1, pk_2, pk_3, tableName, columnName).GetXml();
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		[WebMethod]
		public string GetColumnHistoryTable(string pk_1, string pk_2, string pk_3, string tableName, string columnName) {
			try {
				if ((pk_1 + "") == "") pk_1 = uox.applID + "";
				return RenderGridView(GetColumnHistoryDataSet(pk_1, pk_2, pk_3, tableName, columnName));
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		[WebMethod]
		public string GetStatusHistory(string pk_1, string pk_2, string pk_3, string tableName) {
			try {
				if ((pk_1 + "") == "") pk_1 = uox.applID + "";
				var ds = GetStatusHistoryDataSet(pk_1, pk_2, pk_3, tableName);
				return RenderGridView(ds);
			} catch (Exception exc) { throw new ClientException(exc); }
		}

		private static string RenderGridView(object ds) {
			GridView gv = new GridView();
			gv.RowStyle.CssClass = "NoWrap";
			System.IO.StringWriter stringWrite = new System.IO.StringWriter();
			System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
			gv.DataSource = ds;
			gv.DataBind();
			gv.RenderControl(htmlWrite);
			return stringWrite.ToString();
		}
		public DataSet GetStatusHistoryDataSet(string pk_1, string pk_2, string pk_3, string tableName) {
			return SQL.ExecuteDatasetEx(db, "sp__sh_StatusHistory", pk_1, pk_2, pk_3, tableName);
		}

		public DataSet GetColumnHistoryDataSet(string pk_1, string pk_2, string pk_3, string tableName, string columnName) {
			return SQL.ExecuteDatasetEx(db, "sp__sh_ColumnHistory", pk_1, pk_2, pk_3, tableName, columnName);
		}
		[WebMethod]
		public void Approve() {
			try {
				SQL.ExecuteNonQueryEx(uox.db, "s_MenuStatus_Set", uox.applID, 0, 1, 0);
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		[WebMethod]
		public string Dups() {
			try {
				return uox.Dups();
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		[WebMethod]
		public string DealerList() {
			try {
				return SQL.XmlInnerText(uox.db, SQL.Dealers_V);
			} catch (Exception exc) { throw new ClientException(exc); }
		}
		[WebMethod]
		public string SalesPersons(string dealerID) {
			try {
				DataSet ds = dealerID != null ? uox.DealerSalesPersons(dealerID) : uox.DealerSalesPersons();
				ds.Tables[0].TableName = "Sales";
				DataTable dtSales = ds.Tables["Sales"];
				ds.Tables.Remove(dtSales);
				ds = SQL.ExecuteDatasetEx(uox.db, "SELECT * FROM t_Dealer WHERE ID=@DID", dealerID);
				ds.Tables[0].TableName = "Dealer";
				ds.Tables.Add(dtSales);
				return SQL.XmlInnerText(ds);
			} catch (Exception exc) { throw new ClientException(exc); }
		}

	}
}